USE [JJMC_BPMQA]
GO

/****** Object:  View [dbo].[DDDistributor]    Script Date: 4/15/2020 1:35:47 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[DDDistributor] as 
select
field0001 as Id,
field0002 as GUID,
field0003 as TemporaryAccount,
field0004 as CompanyName,
field0005 as CompanyCode,
en1.showvalue as CompanyLevel,
field0007 as Email,
field0008 as ContactsName,
field0009 as ContactsTelephone,
field0010 as SuperiorDistributor,
field0011 as SuperiorDistributorCode,
en2.SHOWVALUE as CustomerType,
en3.SHOWVALUE as Partnership,
field0014 as CompanyNature,
field0016 as AccountStatus,
en4.SHOWVALUE as ApplicationReason,
om1.name as CreateBy,
field0019 as DealingPerson,
field0020 as ProductLine,
field0021 as DDApproveDate,
field0022 as DDBuList,
om2.name as SaleApplicant,
field0024 as SaleApplicantEmail,
om3.name as LineManager,
field0026 as LineManagerEmail,
field0027 as EndOperationDate,
en5.SHOWVALUE as OpenReason,
field0029 as OldDistributorName,
field0030 as AssociationCompanyName,
at1.FILENAME as AssessNewFileName,
at1.FILENAME as AssessOriginalFileName,
at2.FILENAME as QAExamineNewFileName,
at2.FILENAME as QAExamineOriginalFileName,
at3.FILENAME as BUExamineNewFileName,
at3.FILENAME as BUExamineOriginalFileName,
field0037 as EmailList,
field0038 as DistributorLevelList,
field0039 as Comment,
field0040 as IsActive,
field0041 as IsDelete,
field0042 as CreateTime,
field0043 as Hospitals,
en6.SHOWVALUE as OperatingProvinces
from formmain_0900
left outer join ctp_enum_item en1 on en1.id=field0006
left outer join ctp_enum_item en2 on en2.id=field0012
left outer join ctp_enum_item en3 on en3.id=field0013
left outer join ctp_enum_item en4 on en4.id=field0017
left outer join ctp_enum_item en5 on en5.id=field0028
left outer join ctp_enum_item en6 on en6.id=field0044
left outer join CTP_ATTACHMENT at1 on at1.SUB_REFERENCE=field0032
left outer join CTP_ATTACHMENT at2 on at2.SUB_REFERENCE=field0034
left outer join CTP_ATTACHMENT at3 on at3.SUB_REFERENCE=field0036
left outer join ORG_MEMBER om1 on om1.id=field0018
left outer join ORG_MEMBER om2 on om2.id=field0023
left outer join ORG_MEMBER om3 on om3.id=field0025








GO

